﻿using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model.VRDD.InputDto._0623;
using HCQ2_Model.VRDD.OutputDto._0623;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRDDCircleBaseServiceDAL: IT_VRDDCircleBaseServiceDAL
    {
        /// <summary>
        ///  按商品或店铺获取服务信息
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputServiceByProduct> GetServiceByProduct(InputServiceByProduct param)
        {
            sb?.Clear();
            sb.Append($@"select us.*,rg.rel_id,rel_name=(SELECT TOP 1 file_name FROM dbo.T_DocumentInfo WHERE file_id=rg.rel_id) from (select id,CONVERT(varchar(100),create_date,120) as issue_date,voice_len,
            is_read=(SELECT CAST(COUNT(0) AS BIT) FROM T_VRDDCircleReadService  WHERE du_id={param.user_id} AND s_id=(SELECT id FROM T_VRDDCircleBaseService WHERE s_type=s.bs_type)),
            voice_url,bs_type,file_id,file_name=(SELECT file_name FROM dbo.T_DocumentInfo i WHERE i.file_id=s.file_id) from T_VRDDCircleUserService s where 1=1 ");
            if (param.file_id > 0)
                sb.Append($" and file_id={param.file_id} ");
            else if (param.dd_id > 0)
                sb.Append($" and file_id in(select rel_id from T_VRDDCircleRelGood where dd_id={param.dd_id}) ");
            else if (!string.IsNullOrEmpty(param.folder_path))
                sb.Append($" and file_id in(select file_id from T_DocumentFolderRelation where folder_path like '{param.folder_path}%') ");
            sb.Append(@")us left join (select dd_id,MAX(rel_id)as rel_id from T_VRDDCircleRelGood group by dd_id)rg on us.file_id=rg.dd_id;");
            return SqlHelper.ExecuteList<OutputServiceByProduct>(sb.ToString());
        }
        /// <summary>
        ///  按位置获取服务信息
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputServiceByProduct> GetServiceByGPS(InputServiceByGPS param)
        {
            sb?.Clear();
            sb.Append($@"select us.*,info.file_name,rg.rel_id,rel_name=(SELECT TOP 1 file_name FROM dbo.T_DocumentInfo WHERE file_id=rg.rel_id) from 
            (select  * from (select file_id,file_name,sense=dbo.GetDistance({param.gps_x},{param.gps_y},gps_x,gps_y) from T_DocumentInfo where file_id 
	            in(select file_id from T_DocumentFolderRelation where 1=1 ");
            if (param.folder_id > 0)
                sb.Append($@" and folder_id={param.folder_id} ");
            else if (!string.IsNullOrEmpty(param.folder_path))
                sb.Append($@" and folder_path LIKE '{param.folder_path}%' ");
            sb.Append(@"))i )info inner join 
            (select id,CONVERT(varchar(100),create_date,120) as issue_date,voice_len,voice_url,bs_type,file_id from T_VRDDCircleUserService)us 
            on info.file_id=us.file_id left join(select dd_id,MAX(rel_id)as rel_id from T_VRDDCircleRelGood group by dd_id)rg on us.file_id=rg.dd_id;");
            return SqlHelper.ExecuteList<OutputServiceByProduct>(sb.ToString());
        }
    }
}